﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace TMS
{
    class Categories
    {
        public void CreateCategory1(string cat1_Name)
        {
            
        }

              

        public DataSet GetAllCategory1()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM category_1";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }


        public DataSet GetAllCategory2(string cat1_ID)
        {
            string cat1str;
            if (cat1_ID=="") cat1str="";
            else cat1str=" WHERE cat1_id='" + cat1_ID + "' OR cat1_id='1'";
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM category_2"+cat1str;
            //MessageBox.Show(sqlSelectAll);
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }

        public DataSet GetAllCategory3(string cat1_ID,string cat2_ID)
        {
            string catstr;
            if ((cat1_ID == "")&&(cat2_ID=="")) catstr = "";
            else if ((cat2_ID == "")&&(cat1_ID!="")) catstr = " WHERE cat1_id='" + cat1_ID + "' OR cat1_id='1'";
            else if ((cat1_ID == "")&&(cat2_ID!="")) catstr = " WHERE cat2_id='" + cat2_ID + "' OR cat1_id='1'";
            else catstr = " WHERE cat1_id='" + cat1_ID + "',cat2_ID='" + cat2_ID + "' OR cat1_id='1'";

            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            
            string sqlSelectAll = "SELECT * FROM category_3" + catstr;
           // MessageBox.Show(sqlSelectAll);
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result;
        }

        public string GetCategory1Name(int cat1_ID)
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM category_1 WHERE id='" + cat1_ID + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            return result.Tables[0].Rows[0].ItemArray[0].ToString();

        }

        public void CreateCategory2()
        {

        }

        public void GetCategory2(int cat2_ID)
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();

            string sqlSelectAll = "SELECT * FROM category_2 WHERE id='" + cat2_ID + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);

            DataSet result = new DataSet();
            MyDA.Fill(result);

            mysqlCon.Close();

            //return result.Tables[0].Rows[0].ItemArray[0].ToString();
        }

        public void CreateCategory3()
        {

        }

        public void GetCategory3(int cat1_ID)
        {

        }
    }
}
